/**   
* @Title: DataSourceUtil.java 
* @Package com.kerrykidz.system.util 
* @Description: 
* Copyright: Copyright (c) 2014 
* Company:wuwh team by iss
* @author: zhuss   
* @date: 2014年9月17日 下午4:45:25 
* @version: V1.0
*
*/
package com.kerrykidz.system.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import com.kerrykidz.filemanagement.dao.model.CarCareFormEntity;
import com.kerrykidz.promotion.dao.model.ProCampaignEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmPayformEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmPayformOrderEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmPrdPriceEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmPurOrderEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmPurordPrdEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmStgAdjEntity;
import com.kerrykidz.scminvoicing.dao.model.ScmStgadjPrdEntity;
import com.kerrykidz.system.util.sync.SyncParamHelper;
import com.kerrykidz.system.vo.FormSyncVo;
import com.kerrykidz.system.vo.UserInfo;
import com.kerrykidz.workflow.dao.model.ActivitiEntity;
import com.kerrykidz.workflow.dao.model.HrmLeaveEntity;
import com.kerrykidz.workflow.vo.SearchParamVo;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.rdp.framework.exception.RDPException;

/**
 * 
 * @ClassName: DataSourceUtil 
 * @description: 数据源工具类
 * @author: zhuss 
 * @date: 2014年9月17日 下午4:45:29 
 * @version: V1.0
 *
 */
@Component
public class DataSourceUtil {
	
	@Autowired
	@Qualifier("dataSource2")
	private  ComboPooledDataSource dataSource2;
	
	/**
	 * 获取数据源连接
	 * @return
	 * @throws Exception
	 * @date: 2014年9月17日
	 */
	public Connection getConnection() throws RDPException {
		Connection conn;
		try {
			conn = dataSource2.getConnection();
			return conn;
		} catch (SQLException e) {
			throw new RDPException("网络异常，请检查网络是否通畅!");
		}
	}
	
	/**
	 * 同步请假流程
	 * @param hle
	 * @param flag
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executeLeave(HrmLeaveEntity hle,String type) throws RDPException{
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
        PreparedStatement pstmt = null;
        StringBuffer sql = new StringBuffer();
        List<Object> parameters = new ArrayList<Object>();
        try {
        	 conn.setAutoCommit(false);
        	if(type.equalsIgnoreCase("update")){
        		sql.append("update HRM_LEAVE set PK_LVETYPE_ID=?,LVE_START=?,LVE_END=?,LVE_REASON=?,MODI_BY=?,MODI_TIME=? ");
				sql.append(" where PK_LEAVE_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(hle.getPkLvetypeId());
				parameters.add(hle.getLveStart());
				parameters.add(hle.getLveEnd());
				parameters.add(hle.getLveReason());
				parameters.add(hle.getModiBy());
				parameters.add(hle.getModiTime());
				parameters.add(hle.getPkLeaveId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
        	}else if(type.equalsIgnoreCase("insert")){
        		sql.append("insert HRM_LEAVE(PK_LEAVE_ID, FK_FLOWINST_ID, STATUS, PK_LVETYPE_ID,LVE_START, LVE_END,LVE_REASON,APPLY_BY, APPLY_TIME,FK_BRAN_ID,MODI_BY,MODI_TIME) values(?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(hle.getPkLeaveId());
				parameters.add(hle.getFkFlowinstId());
				parameters.add(hle.getStatus());
				parameters.add(hle.getPkLvetypeId());
				parameters.add(hle.getLveStart());
				parameters.add(hle.getLveEnd());
				parameters.add(hle.getLveReason());
				parameters.add(hle.getApplyBy());
				parameters.add(hle.getApplyTime());
				parameters.add(hle.getFkBranId());
				parameters.add(hle.getModiBy());
				parameters.add(hle.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
        	}
        	conn.commit();
        } catch (Exception e) {
        	try {
				conn.rollback();
			} catch (SQLException e1) {
			}
            throw new RDPException("网络异常，请检查网络是否通畅!"); 
        }finally{
        	free(null, pstmt, conn);
        }
        return noOfRows;
	}
	
	/**
	 * 同步库存调整流程
	 * 
	 * @param se
	 * @param flag
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executeStock(ScmStgAdjEntity se, String type)
			throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update scm_stg_adj set FK_FLOWINST_ID=?,STATUS=?,MODI_BY = ?, MODI_TIME = ? where PK_STGADJ_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(se.getFkFlowinstId());
				parameters.add(se.getStatus());
				parameters.add(se.getModiBy());
				parameters.add(se.getModiTime());
				parameters.add(se.getPkStgadjId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
				// 删除全部
				pstmt = conn
						.prepareStatement("delete from scm_stgadj_prd where FK_STGADJ_ID = ?");
				pstmt.setString(1, se.getPkStgadjId());
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into scm_stg_adj values (?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(se.getPkStgadjId());
				parameters.add(se.getFkFlowinstId());
				parameters.add(se.getStatus());
				parameters.add(se.getFkTskId());
				parameters.add(se.getFkBranId());
				parameters.add(se.getApplyBy());
				parameters.add(se.getApplyTime());
				parameters.add(se.getModiBy());
				parameters.add(se.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			List<ScmStgadjPrdEntity> list = se.getScmStgadjPrd();
			if(null != list){
				for (ScmStgadjPrdEntity sse : list) {
					parameters = new ArrayList<Object>();
					pstmt = conn
							.prepareStatement("insert into scm_stgadj_prd values (?,?,?,?,?,?)");
					parameters.add(sse.getFkStgadjId());
					parameters.add(sse.getFkPrdId());
					parameters.add(sse.getStgCnt());
					parameters.add(sse.getStkCnt());
					parameters.add(sse.getAdjCnt());
					parameters.add(sse.getComments());
					setParameters(pstmt, parameters);
					noOfRows = pstmt.executeUpdate();
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
			}
			throw new RDPException("网络异常，请检查网络是否通畅!"); 
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 同步付款流程
	 * @param sp
	 * @param type
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executePay(ScmPayformEntity sp, String type) throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update scm_payform set FK_BRAN_ID=?,MODI_BY = ?, MODI_TIME = ? where PK_PAYFORM_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(sp.getFkBranId());
				parameters.add(sp.getModiBy());
				parameters.add(sp.getModiTime());
				parameters.add(sp.getPkPayformId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
				// 删除改付款单所有子表数据
				pstmt = conn
						.prepareStatement("delete from scm_stgadj_prd where FK_STGADJ_ID = ?");
				pstmt.setString(1, sp.getPkPayformId());
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into scm_payform values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(sp.getPkPayformId());
				parameters.add(sp.getFkFlowinstId());
				parameters.add(sp.getStatus());
				parameters.add(sp.getFkSupId());
				parameters.add(sp.getCheckNo());
				parameters.add(sp.getCableNo());
				parameters.add(sp.getCreditNo());
				parameters.add(sp.getFkBranId());
				parameters.add(sp.getDelFlag());
				parameters.add(sp.getApplyBy());
				parameters.add(sp.getApplyTime());
				parameters.add(sp.getModiBy());
				parameters.add(sp.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			List<ScmPayformOrderEntity> list = sp.getScmPayformOrderList();
			if(null != list){
				for (ScmPayformOrderEntity spo : list) {
					parameters = new ArrayList<Object>();
					pstmt = conn
							.prepareStatement("insert into scm_payform_order values (?,?)");
					parameters.add(spo.getFkPayformId());
					parameters.add(spo.getFkPurordId());
					setParameters(pstmt, parameters);
					noOfRows = pstmt.executeUpdate();
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RDPException("网络异常，请检查网络是否通畅!");
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 同步采购订单流程
	 * @param so
	 * @param type
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executeOrder(ScmPurOrderEntity so, String type) throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update scm_pur_order set FK_BRAN_ID=?,MODI_BY = ?, MODI_TIME = ? where PK_PURORD_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(so.getFkBranId());
				parameters.add(so.getModiBy());
				parameters.add(so.getModiTime());
				parameters.add(so.getPkPurordId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
				// 删除改付款单所有子表数据
				pstmt = conn
						.prepareStatement("delete from scm_purord_prd where FK_PURORD_ID = ?");
				pstmt.setString(1, so.getPkPurordId());
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into scm_pur_order values (?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(so.getPkPurordId());
				parameters.add(so.getFkFlowinstId());
				parameters.add(so.getStatus());
				parameters.add(so.getDeliveryStatus());
				parameters.add(so.getFkSupId());
				parameters.add(so.getFkBranId());
				parameters.add(so.getDelFlag());
				parameters.add(so.getApplyBy());
				parameters.add(so.getApplyTime());
				parameters.add(so.getModiBy());
				parameters.add(so.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			List<ScmPurordPrdEntity> purOrdPrdList = so.getPurOrdPrdList();
			if(null != purOrdPrdList){
				for (ScmPurordPrdEntity spp : purOrdPrdList) {
					parameters = new ArrayList<Object>();
					pstmt = conn
							.prepareStatement("insert into scm_purord_prd values (?,?,?,?,?,?,?)");
					parameters.add(spp.getFkPurordId());
					parameters.add(spp.getFkSupagmId());
					parameters.add(spp.getFkPrdId());
					parameters.add(spp.getPurCnt());
					parameters.add(spp.getPurPrice());
					if(StringUtils.isBlank(spp.getDelFlag()))
						spp.setDelFlag("0");
					parameters.add(spp.getDelFlag());
					parameters.add(spp.getModiTime());
					setParameters(pstmt, parameters);
					noOfRows = pstmt.executeUpdate();
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RDPException("网络异常，请检查网络是否通畅!");
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 同步限时打折流程
	 * @param pc
	 * @param type
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executeDiscount(ProCampaignEntity pc, String type) throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update pro_campaign set FK_BRAN_ID=?,CAMP_NAME =?,FK_DIS_ID =?,START_DATE =?,END_DATE =?,PRD_SCOPE =?,COMMENTS =?,MODI_BY = ?, MODI_TIME = ? where PK_CAMP_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(pc.getFkBranId());
				parameters.add(pc.getCampName());
				parameters.add(pc.getFkDisId());
				parameters.add(pc.getStartDate());
				parameters.add(pc.getEndDate());
				parameters.add(pc.getPrdScope());
				parameters.add(pc.getComments());
				parameters.add(pc.getModiBy());
				parameters.add(pc.getModiTime());
				parameters.add(pc.getPkCampId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
				// 删除改付款单所有子表数据
				pstmt = conn
						.prepareStatement("delete from pro_camp_prd where FK_CAMP_ID = ?");
				pstmt.setString(1, pc.getPkCampId());
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into pro_campaign values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(pc.getPkCampId());
				parameters.add(pc.getFkBranId());
				parameters.add(pc.getFkFlowinstId());
				parameters.add(pc.getStatus());
				parameters.add(pc.getCampName());
				parameters.add(pc.getFkDisId());
				parameters.add(pc.getStartDate());
				parameters.add(pc.getEndDate());
				parameters.add(pc.getPrdScope());
				parameters.add(pc.getComments());
				if(StringUtils.isBlank(pc.getDelFlag()))
					pc.setDelFlag("0");
				parameters.add(pc.getDelFlag());
				parameters.add(pc.getModiBy());
				parameters.add(pc.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			List<String> list = pc.getPrdlist();
			if(null != list){
				for (String str : list) {
					parameters = new ArrayList<Object>();
					pstmt = conn
							.prepareStatement("insert into pro_camp_prd values (?,?)");
					parameters.add(pc.getPkCampId());
					parameters.add(str);
					setParameters(pstmt, parameters);
					noOfRows = pstmt.executeUpdate();
				}
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RDPException("网络异常，请检查网络是否通畅!");
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 同步价格定义流程
	 * @param sp
	 * @param type
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executePrice(ScmPrdPriceEntity sp, String type) throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update scm_prd_price set BRANCH_PRICE=?,MODI_BY = ?, MODI_TIME = ? where PK_PRICE_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(sp.getBranchPrice());
				parameters.add(sp.getModiBy());
				parameters.add(sp.getModiTime());
				parameters.add(sp.getPkPriceId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into scm_prd_price values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(sp.getPkPriceId());
				parameters.add(sp.getFkPrdId());
				parameters.add(sp.getFkFlowinstId());
				parameters.add(sp.getStatus());
				parameters.add(sp.getMarketPrice());
				parameters.add(sp.getBranchPrice());
				parameters.add(sp.getComments());
				parameters.add(sp.getValidFrom());
				parameters.add(sp.getValidTo());
				parameters.add(sp.getFkBranId());
				parameters.add(sp.getApplyBy());
				parameters.add(sp.getApplyTime());
				parameters.add(sp.getModiBy());
				parameters.add(sp.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RDPException("网络异常，请检查网络是否通畅!");
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 同步客户服务流程
	 * @param cf
	 * @param type
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月19日
	 */
	public int executeServer(CarCareFormEntity cf, String type) throws RDPException {
		if(SyncParamHelper.CURRENT_STORE.equals("ZB")){//如果是总部就跳出
			return 1;
		}
		int noOfRows = 0;
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		List<Object> parameters = new ArrayList<Object>();
		try {
			conn.setAutoCommit(false);
			if (type.equalsIgnoreCase("update")) {
				sql.append("update CAR_CARE_FORM set channel=?,CUS_NAME=?,TEL=?,MOBILE=?,COMMENTS=?,MODI_BY = ?, MODI_TIME = ? where PK_CAREFORM_ID=?");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(cf.getChannel());
				parameters.add(cf.getCusName());
				parameters.add(cf.getTel());
				parameters.add(cf.getMobile());
				parameters.add(cf.getComments());
				parameters.add(cf.getModiBy());
				parameters.add(cf.getModiTime());
				parameters.add(cf.getPkCareformId());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			} else if (type.equalsIgnoreCase("insert")) {
				sql.append("insert into CAR_CARE_FORM values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt = conn.prepareStatement(sql.toString());
				parameters.add(cf.getPkCareformId());
				parameters.add(cf.getFkMemId());
				parameters.add(cf.getFkFlowinstId());
				parameters.add(cf.getStatus());
				parameters.add(cf.getCareType());
				parameters.add(cf.getChannel());
				parameters.add(cf.getCusName());
				parameters.add(cf.getTel());
				parameters.add(cf.getMobile());
				parameters.add(cf.getComments());
				parameters.add(cf.getFkBranId());
				parameters.add(cf.getApplyBy());
				parameters.add(cf.getApplyTime());
				parameters.add(cf.getModiBy());
				parameters.add(cf.getModiTime());
				setParameters(pstmt, parameters);
				noOfRows = pstmt.executeUpdate();
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RDPException("网络异常，请检查网络是否通畅!");
		} finally {
			free(null, pstmt, conn);
		}
		return noOfRows;
	}
	
	/**
	 * 获取流程实例的当前任务信息 
	 * @param procId
	 * @param userId
	 * @param fkBranId
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月30日
	 */
	public ActivitiEntity queryCurrtTaskByProcId(String procId,UserInfo user,int flag)throws RDPException{
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ActivitiEntity ae = null;
		try {
			String sql = "SELECT art.ID_,art.EXECUTION_ID_,art.PROC_INST_ID_,art.PROC_DEF_ID_,art.NAME_,art.TASK_DEF_KEY_,su.usr_name, "
			+" case " 
				+" when art.ASSIGNEE_=? then '1' " 
				+" when (select count(*) from act_ru_identitylink x where x.user_id_ =? and x.task_id_ =art.id_ ) >0 then '1' " 
				+" when (select count(*) from act_ru_identitylink x where x.group_id_ in (select  fk_role_id from (select  fk_role_id ,fk_usr_id,'-1' PROC_DEF_ID from sec_role2usr union all select FK_ROLE_ID,wa.TO_USR,wa.PROC_DEF_ID from wfm_authorize wa where now() between wa.FROM_DATE and wa.TO_DATE and wa.DEL_FLAG != '1'  ) ru where ru.fk_usr_id = ? and if(ru.PROC_DEF_ID='-1','1=1',ru.PROC_DEF_ID =art.PROC_DEF_ID_) and x.task_id_ =art.id_  ))>0 then '1'  "  
				+" when (select count(*) from wfm_authorize x where x.to_usr = ? and now() between x.FROM_DATE and x.TO_DATE and x.DEL_FLAG != '1' and x.fk_role_id is null and x.from_usr = art.ASSIGNEE_)>0 then '1' "  
				+" else '0' "  
			+" end  isCurrtOpt " ;
			if(flag==1)
				sql +=" FROM (select * from (select * from act_hi_taskinst order by start_time_ desc) x group by x.proc_inst_id_) art " ;
			else
				sql +=" FROM act_ru_task art " ;
			sql +=" left join sec_usr su on su.pk_usr_id = art.ASSIGNEE_  where art.PROC_INST_ID_ = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUserId());
			pstmt.setString(2, user.getUserId());
			pstmt.setString(3, user.getUserId());
			pstmt.setString(4, user.getUserId());
			pstmt.setString(5, procId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ae = new ActivitiEntity();
				ae.setId(rs.getString("ID_"));
				ae.setExecutionId(rs.getString("EXECUTION_ID_"));
				ae.setProcDefId(rs.getString("PROC_INST_ID_"));
				ae.setProcInstId(rs.getString("PROC_DEF_ID_"));
				ae.setName(rs.getString("NAME_"));
				ae.setTaskDefKey(rs.getString("TASK_DEF_KEY_"));
				ae.setUserName(rs.getString("usr_name"));
				ae.setIsCurrtOpt(rs.getString("isCurrtOpt"));
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return ae;
	}
	
	/**
	 * 获取历史处理记录
	 * @param procId
	 * @param fkBranId
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月30日
	 */
	public List<ActivitiEntity> queryHisOptByProcId(String procId,UserInfo user)throws RDPException{
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ActivitiEntity ae = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			String sql = "SELECT ahc.ID_,ahc.TIME_,ahc.TASK_ID_,ahc.PROC_INST_ID_,ahc.MESSAGE_,ahv.text_,su.usr_name "
		+"FROM act_hi_comment ahc "
		+"left join sec_usr su on su.pk_usr_id = ahc.user_id_ "
		+"left join act_hi_varinst ahv on ahv.PROC_INST_ID_ = ahc.PROC_INST_ID_ and ahv.name_ =( ahc.task_id_+'handleReason') "
		+"where ahc.PROC_INST_ID_ = ? order by ahc.TIME_";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, procId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ae = new ActivitiEntity();
				ae.setId(rs.getString("ID_"));
				ae.setTime(rs.getTimestamp("TIME_"));
				ae.setTaskId(rs.getString("TASK_ID_"));
				ae.setMessage(rs.getString("MESSAGE_"));
				ae.setProcDefId(rs.getString("PROC_INST_ID_"));
				ae.setUserName(rs.getString("usr_name"));
				ae.setText(rs.getString("text_"));
				list.add(ae);
			}
			if(null == ae)
				return null;
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 获取所有的流程定义
	 * @param fkBranId
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月30日
	 */
	public List<ActivitiEntity> queryProcDef(String fkBranId)throws RDPException{
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ActivitiEntity ae = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			String sql = "select def.id_,def.name_,def.key_ from (select * from act_re_procdef x order by x.version_ desc) def group by def.key_";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ae = new ActivitiEntity();
				ae.setId(rs.getString("ID_"));
				ae.setName(rs.getString("name_"));
				ae.setKey(rs.getString("key_"));
				list.add(ae);
			}
			if(null == ae)
				return null;
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 我的待办任务总数
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月28日
	 */
	public int queryTodoTaskCount(SearchParamVo searchParam) throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int sumC = 0;
		try {
			String sql = "select count(*) sumCount from act_hi_taskinst aht inner join act_hi_procinst ahp on aht.proc_inst_id_ = ahp.id_ inner join act_re_procdef arp on aht.proc_def_id_ = arp.id_ inner join sec_usr su on ahp.start_user_id_ = su.pk_usr_id inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = aht.ID_ where aht.end_time_ is null and aht.ASSIGNEE_ = ? or I.USER_ID_ = ? or I.GROUP_ID_ in (select  fk_role_id from (select  fk_role_id ,fk_usr_id,'-1' PROC_DEF_ID from sec_role2usr union all select FK_ROLE_ID,wa.TO_USR,wa.PROC_DEF_ID from wfm_authorize wa where now() between wa.FROM_DATE and wa.TO_DATE and wa.DEL_FLAG != '1'  ) ru where ru.fk_usr_id = ? and if(ru.PROC_DEF_ID='-1','1=1',ru.PROC_DEF_ID =aht.PROC_DEF_ID_))  ";
			if(StringUtils.isNotBlank(searchParam.getOrder()))
				sql += " and ahp.BUSINESS_KEY_ like '%"+searchParam.getOrder()+"%'";
			if(StringUtils.isNotBlank(searchParam.getType()))
				sql += " and arp.key_ = '"+searchParam.getType()+"'";
			String userId = searchParam.getUserInfo().getUserId();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			pstmt.setString(2, userId);
			pstmt.setString(3, userId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				sumC = rs.getInt("sumCount");
			}
		} catch (Exception e) {
			return 0;
		} finally {
			free(rs, pstmt, conn);
		}
		return sumC;
	}
	
	/**
	 * 我的待办任务
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月28日
	 */
	public List<ActivitiEntity> queryTodoTask(SearchParamVo searchParam) throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ActivitiEntity ae = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			String sql = "select * from (select ahp.BUSINESS_KEY_,arp.name_ PROC_DEF_NAME,aht.name_ ACTIVITY_NAME,su.usr_name,arp.key_,aht.CREATE_TIME_  from act_ru_task aht "
					+ "inner join act_hi_procinst ahp on aht.proc_inst_id_ = ahp.id_ "
					+ "inner join act_re_procdef arp on aht.proc_def_id_ = arp.id_ "
					+ "inner join sec_usr su on ahp.start_user_id_ = su.pk_usr_id "
					+ "inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = aht.ID_ "
					+ "where aht.ASSIGNEE_ = ? or I.USER_ID_ = ? or I.GROUP_ID_ in (select  fk_role_id from (select  fk_role_id ,fk_usr_id,'-1' PROC_DEF_ID from sec_role2usr union all select FK_ROLE_ID,wa.TO_USR,wa.PROC_DEF_ID from wfm_authorize wa where now() between wa.FROM_DATE and wa.TO_DATE and wa.DEL_FLAG != '1'  ) ru where ru.fk_usr_id = ? and if(ru.PROC_DEF_ID='-1','1=1',ru.PROC_DEF_ID =aht.PROC_DEF_ID_)) "
					+ "union "
					+" select ahp.BUSINESS_KEY_,arp.name_ PROC_DEF_NAME,aht.name_ ACTIVITY_NAME,su.usr_name,arp.key_,aht.CREATE_TIME_   from act_ru_task aht "
					+ "inner join act_hi_procinst ahp on aht.proc_inst_id_ = ahp.id_ "
					+ "inner join act_re_procdef arp on aht.proc_def_id_ = arp.id_ "
					+ "inner join sec_usr su on ahp.start_user_id_ = su.pk_usr_id "
					+" where aht.proc_def_id_ like concat('%','server','%') and aht.ASSIGNEE_ = ? "
					+ ") todo where 1=1 ";
			if(StringUtils.isNotBlank(searchParam.getOrder()))
				sql += " and todo.BUSINESS_KEY_ like '%"+searchParam.getOrder()+"%'";
			if(StringUtils.isNotBlank(searchParam.getType()))
				sql += " and todo.key_ = '"+searchParam.getType()+"'";
			sql +=" order by todo.CREATE_TIME_ desc";
			if(searchParam.getPageNo() != 0 && searchParam.getPageSize() != 0){
				int start = (searchParam.getPageNo() -1)*searchParam.getPageSize();
				sql += " limit "+start+","+searchParam.getPageSize()+"";
			}	
			String userId = searchParam.getUserInfo().getUserId();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			pstmt.setString(2, userId);
			pstmt.setString(3, userId);
			pstmt.setString(4, userId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ae = new ActivitiEntity();
				ae.setBusinessKey(rs.getString("BUSINESS_KEY_"));
				ae.setProcDefName(rs.getString("PROC_DEF_NAME"));
				ae.setActivityName(rs.getString("ACTIVITY_NAME"));
				ae.setUserName(rs.getString("usr_name"));
				ae.setKey(rs.getString("key_"));
				list.add(ae);
			}
			if(null == ae)
				return null;
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 我的已办任务
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月28日
	 */
	public int queryFinishTaskCount(SearchParamVo searchParam) throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int sumC = 0;
		try {
			String sql = "select count(*) sumCount "
		+"from (select * from (select * from act_hi_comment order by id_ desc) x group by x.user_id_,x.proc_inst_id_  ) ahc "
		+"inner join act_hi_procinst ahp on ahc.proc_inst_id_ = ahp.id_ "
		+"inner join act_re_procdef arp on ahp.proc_def_id_ = arp.id_ "
		+"inner join sec_usr su on ahp.start_user_id_ = su.pk_usr_id "
		+"where ahc.user_id_ = ?";
			if(StringUtils.isNotBlank(searchParam.getOrder()))
				sql += " and ahp.BUSINESS_KEY_ like '%"+searchParam.getOrder()+"%'";
			if(StringUtils.isNotBlank(searchParam.getType()))
				sql += " and arp.key_ = '"+searchParam.getType()+"'";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, searchParam.getUserInfo().getUserId());
			rs = pstmt.executeQuery();
			while(rs.next()){
				sumC = rs.getInt("sumCount");
			}
		} catch (Exception e) {
			return 0;
		} finally {
			free(rs, pstmt, conn);
		}
		return sumC;
	}
	
	/**
	 * 我的已办任务
	 * @return
	 * @throws RDPException
	 * @date: 2014年9月28日
	 */
	public List<ActivitiEntity> queryFinishTask(SearchParamVo searchParam) throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ActivitiEntity ae = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			String sql = "select ahp.BUSINESS_KEY_,arp.name_ PROC_DEF_NAME,ahc.message_,su.usr_name,ahc.time_,arp.key_  "
		+"from (select * from (select * from act_hi_comment order by id_ desc) x group by x.user_id_,x.proc_inst_id_  ) ahc "
		+"inner join act_hi_procinst ahp on ahc.proc_inst_id_ = ahp.id_ "
		+"inner join act_re_procdef arp on ahp.proc_def_id_ = arp.id_ "
		+"inner join sec_usr su on ahp.start_user_id_ = su.pk_usr_id "
		+"where ahc.user_id_ = ?";
			if(StringUtils.isNotBlank(searchParam.getOrder()))
				sql += " and ahp.BUSINESS_KEY_ like '%"+searchParam.getOrder()+"%'";
			if(StringUtils.isNotBlank(searchParam.getType()))
				sql += " and arp.key_ = '"+searchParam.getType()+"'";
			sql += "order by ahc.time_ desc";
			if(searchParam.getPageNo() != 0 && searchParam.getPageSize() != 0){
				int start = (searchParam.getPageNo() -1)*searchParam.getPageSize();
				sql += " limit "+start+","+searchParam.getPageSize()+"";
			}	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, searchParam.getUserInfo().getUserId());
			rs = pstmt.executeQuery();
			while(rs.next()){
				ae = new ActivitiEntity();
				ae.setBusinessKey(rs.getString("BUSINESS_KEY_"));
				ae.setProcDefName(rs.getString("PROC_DEF_NAME"));
				ae.setMessage(rs.getString("message_"));
				ae.setUserName(rs.getString("usr_name"));
				ae.setTime(rs.getTimestamp("time_"));
				ae.setKey(rs.getString("key_"));
				list.add(ae);
			}
			if(null == ae)
				return null;
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**===========定时下载任务所需的数据查询=============*/
	
	/**
	 * 查询所有的act_re_procdef
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryProcdef()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_re_procdef");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setCategory(rs.getString("CATEGORY_"));
				ae.setName(rs.getString("NAME_"));
				ae.setKey(rs.getString("KEY_"));
				ae.setVersion(rs.getString("VERSION_"));
				ae.setDeploymentId(rs.getString("DEPLOYMENT_ID_"));
				ae.setResourceName(rs.getString("RESOURCE_NAME_"));
				ae.setDgrmResourceName(rs.getString("DGRM_RESOURCE_NAME_"));
				ae.setDescrption(rs.getString("DESCRIPTION_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	/**
	 * 查询所有的act_ru_execution
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryExecution()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_ru_execution");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setBusinessKey(rs.getString("BUSINESS_KEY_"));
				ae.setProcDefId(rs.getString("PROC_DEF_ID_"));
				ae.setActId(rs.getString("ACT_ID_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_ru_task
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryTask()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_ru_task");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setExecutionId(rs.getString("EXECUTION_ID_")); 
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setProcDefId(rs.getString("PROC_DEF_ID_"));
				ae.setName(rs.getString("NAME_"));
				ae.setTaskDefKey(rs.getString("TASK_DEF_KEY_"));
				ae.setAssignee(rs.getString("ASSIGNEE_"));
				ae.setCreateTime(rs.getTimestamp("CREATE_TIME_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_ru_identitylink
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryIdentitylink()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_ru_identitylink");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setGroupId(rs.getString("GROUP_ID_"));
				ae.setType(rs.getString("TYPE_"));
				ae.setUserId(rs.getString("USER_ID_"));
				ae.setTaskId(rs.getString("TASK_ID_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setProcDefId(rs.getString("PROC_DEF_ID_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_hi_comment
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryComment()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_hi_comment");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setType(rs.getString("TYPE_"));
				ae.setTime(rs.getTimestamp("TIME_"));
				ae.setUserId(rs.getString("USER_ID_"));
				ae.setTaskId(rs.getString("TASK_ID_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setMessage(rs.getString("MESSAGE_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_hi_varinst
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryVarinst()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_hi_varinst");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setExecutionId(rs.getString("EXECUTION_ID_"));
				ae.setTaskId(rs.getString("TASK_ID_"));
				ae.setName(rs.getString("NAME_"));
				ae.setVarType(rs.getString("VAR_TYPE_"));
				ae.setDoubleValue(rs.getDouble("DOUBLE_"));
				ae.setLongValue(rs.getLong("LONG_"));
				ae.setText(rs.getString("TEXT_"));
				ae.setCreateTime(rs.getTimestamp("CREATE_TIME_"));
				ae.setUpdateTime(rs.getTimestamp("LAST_UPDATED_TIME_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_hi_taskinst
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryTaskinst()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_hi_taskinst");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setProcDefId(rs.getString("PROC_DEF_ID_"));
				ae.setTaskDefKey(rs.getString("TASK_DEF_KEY_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setExecutionId(rs.getString("EXECUTION_ID_")); 
				ae.setName(rs.getString("NAME_"));
				ae.setAssignee(rs.getString("ASSIGNEE_"));
				ae.setStartTime(rs.getTimestamp("START_TIME_"));
				ae.setClaimTime(rs.getTimestamp("CLAIM_TIME_"));
				ae.setEndTime(rs.getTimestamp("END_TIME_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_hi_procinst
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryProcinst()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_hi_procinst");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				ae.setBusinessKey(rs.getString("BUSINESS_KEY_"));
				ae.setProcDefId(rs.getString("PROC_DEF_ID_"));
				ae.setStartTime(rs.getTimestamp("START_TIME_"));
				ae.setEndTime(rs.getTimestamp("END_TIME_"));
				ae.setStartUserId(rs.getString("START_USER_ID_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询所有的act_hi_identitylink
	 * @return
	 * @throws RDPException
	 * @date: 2014年10月9日
	 */
	public List<ActivitiEntity> queryHiIdentitylink()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ActivitiEntity> list = new ArrayList<ActivitiEntity>();
		try {
			pstmt = conn.prepareStatement("select * from act_hi_identitylink");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ActivitiEntity ae = new ActivitiEntity();
				ae.setId(rs.getString("id_"));
				ae.setGroupId(rs.getString("GROUP_ID_"));
				ae.setType(rs.getString("TYPE_"));
				ae.setUserId(rs.getString("USER_ID_"));
				ae.setTaskId(rs.getString("TASK_ID_"));
				ae.setProcInstId(rs.getString("PROC_INST_ID_"));
				list.add(ae);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}

	/**
	 * 查询总部的表单数据
	 * @param sql :查询数据sql
	 * @param pkId:主键字段名
	 * @param tableName：表名
	 * @return
	 * @throws RDPException
	 * @date: 2014年11月4日
	 */
	public List<FormSyncVo> queryFormDate(String sql,String pkId,String tableName)throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<FormSyncVo> list = new ArrayList<FormSyncVo>();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				FormSyncVo vo = new FormSyncVo();
				vo.setPkId(pkId);
				vo.setTableName(tableName);
				vo.setId(rs.getString("id_"));
				vo.setStatus(rs.getString("STATUS"));
				list.add(vo);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 查询总部的库存数据
	 * @return
	 * @throws RDPException
	 * @date: 2014年11月4日
	 */
	public List<FormSyncVo> queryStockDate()throws RDPException {
		Connection conn = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<FormSyncVo> list = new ArrayList<FormSyncVo>();
		try {
			pstmt = conn.prepareStatement("select FK_PRD_ID,STG_CNT from scm_prd_stg");
			rs = pstmt.executeQuery();
			while(rs.next()){
				FormSyncVo vo = new FormSyncVo();
				vo.setId(rs.getString("FK_PRD_ID"));
				vo.setStgCnt(rs.getInt("STG_CNT"));
				list.add(vo);
			}
		} catch (Exception e) {
			return null;
		} finally {
			free(rs, pstmt, conn);
		}
		return list;
	}
	
	
	/**
     * 方法，为sql语句设置参数；
     * @param pstmt 指令
     * @param parameters 参数集合；
     * @throws SQLException 
     */
	private void setParameters(PreparedStatement pstmt,List<Object> parameters) throws Exception{
        for (int i = 0; i < parameters.size(); i++) {
            Object v = parameters.get(i);
            pstmt.setObject(i+1, v);
        }
    }
    
    /**
     * 释放资源
     * @param resultset
     * @param st
     * @param conn
     * @date: 2014年9月23日
     */
	public void free(ResultSet resultset, PreparedStatement pstmt,
			Connection conn) {
		try {
			if (resultset != null)
				resultset.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
	}
}
